package com.itheima.dao;

import com.github.pagehelper.Page;
import com.itheima.POJO.Member;

import com.itheima.POJO.MemberDies;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;


import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.annotations.*;


import java.util.HashMap;
import java.util.List;
import java.util.Map;

public interface memberMapper {
    @Select("SELECT * FROM t_member WHERE phoneNumber = #{telephone}")
    Member findMemberBYtelephone(String telephone);

    @Insert(" insert into t_member (phoneNumber,regTime) values (#{phoneNumber},#{regTime})")
    void addMember(Member member);

    @Select("SELECT  COUNT(*) FROM t_member WHERE regTime LIKE concat('%',#{repalce},'%')")
    Integer findMemberCountByMouth(String replace);

    @Select("SELECT COUNT(*) FROM t_member WHERE regTime=#{dateString}")
    Integer findCountByRegTime(String dateString);

    @Select(" SELECT COUNT(*) FROM t_member WHERE regTime LIKE concat('%',#{yueDate},'%')")
    Integer findCountByredtime(String yueDate);

    @Select(" SELECT COUNT(*) FROM t_member WHERE regTime  BETWEEN #{startDay} AND #{endDay}")
    Integer findCountByredtimeWeek(HashMap<String, String> hashMap);

    @Select(" SELECT COUNT(*) FROM t_member")
    Integer findAllmemberCount();



    /**
     * 根据姓名和档案号查询member
     * @param member
     * @return
     */
    @Select("select * from t_member where name=#{name} and fileNumber=#{fileNumber}")
    Member findMemberByMemberNameAndFileNumber(Member member);

    @Select("<script>select * from t_member\n" +
            "    <where>\n" +
            "       <if test=\"value !=null and value.length > 0\">\n" +
            "         fileNumber  = #{value} or name = #{value}\n" +
            "        </if>\n" +
            "    </where>" +
            "</script>")
    Page<Member> findByCondition(String queryString);

    @Select("SELECT th.`keyword` FROM t_member_healthscheme tmh,t_healthscheme th WHERE th.`id`=tmh.`healthprogram_id` AND tmh.`member_id`=#{id}")
    List<String> findAllhkeyword(Integer id);

    @Select("SELECT m.fileNumber,m.name,m.sex,m.age FROM t_member m WHERE id = #{id}")
    Member findOneById(Integer id);

    @Select("SELECT * FROM t_member_dies WHERE id = #{id}")
    MemberDies findHistoryById(Integer id);

    @Select("SELECT fileNumber FROM t_member WHERE id = #{id}")
    String findFileNumberById(Integer id);

    @Select("SELECT mh.healthprogram_id FROM t_member_healthscheme mh  WHERE mh.member_id = #{id}")
    List<Integer> findHealthProgramIdsByMemberId(Integer id);

    @Update("UPDATE t_member SET healthManager = #{healthManager} WHERE fileNumber = #{fileNumber}")
    void healthEdit(Member member);

    @Select("SELECT id FROM t_member WHERE fileNumber = #{fileNumber}")
    int findIdByFileNumber(String fileNumber);

    @Select("SELECT healthprogram_id FROM t_member_healthscheme WHERE member_id = #{memberId}")
    List<Integer> findHealthByMemberId(int memberId);

    @Delete("DELETE FROM t_member_healthscheme WHERE member_id = #{memberId}")
    void deleteHealthProgramIdByMemberId(int memberId);

    @Insert("INSERT INTO t_member_healthscheme VALUES(#{memberId},#{healthProgramId})")
    void setMemberIdAndHealthProgramId(Map map);

    @Delete("DELETE FROM t_member WHERE id = #{id}")
    void deleteMemberById(Integer id);

    @Delete("DELETE FROM t_member_dies WHERE id = #{id}")
    void deleteDiesById(Integer id);

    @Delete("DELETE FROM t_order WHERE member_id = #{id}")
    void deleteOrderById(Integer id);

    @Delete("DELETE FROM t_memberinfo WHERE id = #{id}")
    void deleteMemberInfoById(Integer id);

    @Insert("insert into t_member(fileNumber,name,idCard,phoneNumber,regTime,password,email,birthday,remark,age,marriage,nation,healthManager,profrssion,birthplace,fileStatus,enducation) values(#{fileNumber},#{name},#{idCard},#{phoneNumber},#{regTime},#{password},#{email},#{birthday},#{remark},#{age},#{marriage},#{nation},#{healthManager},#{profrssion},#{birthplace},#{fileStatus},#{enducation})")
    @SelectKey(keyProperty = "id",statement = "select last_insert_id()",resultType = int.class,before = false)
    void addAllMember(com.itheima.VO.Member member);

    @Insert("insert into t_member_dies(id,previous,family,allergy,medical,chronic) values(#{id},#{previous},#{family},#{allergy},#{medical},#{chronic})")
    void addMemberDies(com.itheima.VO.Member memberDies);

    @Insert("insert into t_memberinfo(id,height,weight,dbp,sysp,breathe,animal_heat,waistline,hipline,BMI,oxyhemoglobin_saturation) values(#{id},#{height},#{weight},#{dbp},#{sysp},#{breathe},#{animal_heat},#{waistline},#{hipline},#{BMI},#{oxyhemoglobin_saturation})")
    void addMemberInfo( com.itheima.VO.Member memberInfo);


    @Select("<script>select * from t_member\n" +
            "    <where>\n" +
            "       <if test=\"value !=null and value.length > 0\">\n" +
            "         fileNumber  = #{value} or name like concat('%',#{value},'%') or phoneNumber = #{value})\n" +
            "        </if>\n" +
            "    </where>" +
            "</script>")
    Page<com.itheima.VO.Member> findMember( String queryString);

    @Select("SELECT * FROM t_member m,t_member_dies md,t_memberinfo mi WHERE m.id=#{id} AND md.id=#{id} and mi.id=#{id}")
    com.itheima.VO.Member findMemberById(Integer id);

    @Update("<script>update t_member <set> <if test=\"fileNumber != null\"> fileNumber = #{fileNumber}, </if> <if test=\"name != null\"> name = #{name}, </if> <if test=\"idCard != null\"> idCard = #{idCard}, </if> <if test=\"phoneNumber != null\"> phoneNumber = #{phoneNumber}, </if> <if test=\"regTime != null\"> regTime = #{regTime}, </if> <if test=\"password != null\"> password = #{password},</if> <if test=\"email != null\"> email = #{email},</if> <if test=\"birthday != null\"> birthday = #{birthday},</if> <if test=\"remark != null\"> remark = #{remark},</if> <if test=\"age != null\"> age = #{age},</if> <if test=\"marriage != null\"> marriage = #{marriage},</if> <if test=\"nation != null\"> nation = #{nation},</if> <if test=\"healthManager != null\"> healthManager = #{healthManager},</if> <if test=\"profrssion != null\"> profrssion = #{profrssion},</if> <if test=\"birthplace != null\"> birthplace = #{birthplace},</if> <if test=\"fileStatus != null\"> fileStatus = #{fileStatus},</if> <if test=\"enducation != null\"> enducation = #{enducation}, </if></set> where id = #{id}</script>")
    void editMember(com.itheima.VO.Member member);

    @Update("<script>update t_member_dies <set> <if test=\"previous != null\"> previous = #{previous}, </if> <if test=\"family != null\"> family = #{family}, </if> <if test=\"allergy != null\"> allergy = #{allergy}, </if> <if test=\"medical != null\"> medical = #{medical}, </if> <if test=\"chronic != null\"> chronic = #{chronic}, </if></set> where id = #{id}</script>")
    void editMemberDies(com.itheima.VO.Member member);

    @Update("<script>update t_memberinfo <set> <if test=\"height != null\"> height = #{height}, </if> <if test=\"weight != null\"> weight = #{weight}, </if> <if test=\"dbp != null\"> dbp = #{dbp}, </if> <if test=\"sysp != null\"> sysp = #{sysp}, </if> <if test=\"breathe != null\"> breathe = #{breathe},</if> <if test=\"animal_heat != null\"> animal_heat = #{animal_heat},</if> <if test=\"waistline != null\"> waistline = #{waistline},</if> <if test=\"hipline != null\"> hipline = #{hipline},</if> <if test=\"BMI != null\"> BMI = #{BMI},</if> <if test=\"oxyhemoglobin_saturation != null\"> oxyhemoglobin_saturation = #{oxyhemoglobin_saturation}, </if></set> where id = #{id}</script>")
    void editMemberInfo(com.itheima.VO.Member member);

    @Delete("delete from t_member where t_member.id=#{id}")
    void deleteMember(Integer id);

    @Delete("delete from t_memberinfo where t_memberinfo.id=#{id}")
    void deleteMemberInfo(Integer id);

    @Delete("delete from t_member_dies where t_member_dies.id=#{id}")
    void deleteMemberDies(Integer id);
}
